# -*- coding: utf-8 -*-
# @Author  : Bink
# @Email   : 2641032316@qq.com
# @Time    : 2020/10/10 17:06
# @File    : 作业1.py

# todo
#  学生id，消费类别，消费地点，消费方式，消费时间，消费金额，剩余金额
#  从一卡通card.txt文件中，取出30个学生的消费记录，分析最大消费，最低消费，平均消费，最大占比消费项。

import pandas as pd

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('max_colwidth', 100)


# file_in = pd.read_table('../resources/train/card.txt', delimiter=',', dtype=str)
file_in = pd.read_table('../resources/train/card_train.txt', delimiter=',', dtype=str)

df = pd.DataFrame(file_in).dropna()
df.columns = ['id', 'classification', 'position', 'payment', 'time', 'cost', 'balance']
df['id'] = df['id'].astype(int)
df['cost'] = df['cost'].astype(float)

ids = sorted(set(df['id'].values.tolist()))[:30]
df = df[df['id'] <= max(ids)]

df_groupedbyid = df.groupby(df['id'])
df_id_payment_sum = df['cost'].groupby([df['id'], df['payment']]).sum()
# print(df_id_payment_sum)
df_id_payment_count = df['payment'].groupby(df['id']).value_counts()
# print(df_id_payment_count)
df_id_sum = df['cost'].groupby(df['id']).sum()
# print(df_id_sum)

df_id_payment = pd.DataFrame([df_id_payment_sum, df_id_payment_count, df_id_payment_sum/df_id_payment_count], index=['cost', 'count', 'avg']).swapaxes(1, 0)
# print("平均消费信息：")
# print(df_id_payment)
df_id = df_id_payment.reset_index().iloc[:, :3]
# print("最大消费信息：")
df_id_cost_max = df_id.groupby('id').max('cost')
df_id_cost_max = pd.merge(df_id, df_id_cost_max, on=['id', 'cost'], how='inner').set_index('id').merge(round(df_id_cost_max['cost']/df_id_sum, 2), on=['id'])
df_id_cost_max.columns = ['payment', 'cost', 'maxRate']
# print(df_id_cost_max)
# print("最小消费信息：")
df_id_cost_min = df_id.groupby('id').min('cost')
df_id_cost_min = pd.merge(df_id, df_id_cost_min, on=['id', 'cost'], how='inner').set_index('id').merge(round(df_id_cost_min['cost']/df_id_sum, 6), on=['id'])
df_id_cost_min.columns = ['payment', 'cost', 'minRate']
# print(df_id_cost_min)

df = pd.merge(df_id_payment, df_id_cost_max, on=['id', 'payment', 'cost'], how='left')\
    .merge(df_id_cost_min, on=['id', 'payment', 'cost'], how='left')\
    .reset_index().set_index(['id', 'payment'])

print(df)

# print(pd.merge(df_id_payment, df_id_cost_max, on=['id', 'payment', 'cost'], how='left').reset_index(['id', 'payment']))
